<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/*
 * @name Tickets_model
 * @type Model
 * @autor StackFeed LLC
 * @version 2.3
 *
 * @description - Tickets model will run all of the queries that
 * a user will need to interract with a ticket.
 */
class Tickets_model extends CI_Model 
{
	/*
	 * Default constructor.
	 */
	function __construct()
	{
		parent::__construct();
	}
	
	/*
	 * Get all of the tickets open to a company for the main
	 * tickets view page.
	 * 
	 * @company_id The ID of the company that is being queried.
	 */
	function get_company_open_tickets($company_id)
	{
		$sql = "SELECT	t.tickets_id,
						t.tickets_title,
						(SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_status) tickets_status_description,
						(SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_severity) tickets_severity_description,
						t.tickets_desired_completion
	   			   FROM tickets t
	    		  WHERE t.tickets_company_id = {$company_id}
		            AND t.tickets_status <> 103
	    	   ORDER BY t.tickets_id DESC;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
			return false;
		
		return $query->result_array();
	}
	
	/*
	 * Getting the information when viewing a single ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 */
	function get_individual_ticket($company_id, $ticket_id)
	{
		$sql = "SELECT  t.tickets_id,
					    t.tickets_title,
						(SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_status) tickets_status_description,
						(SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_severity) tickets_severity_description,
						t.tickets_severity,
						t.tickets_hours_allotted,
						t.tickets_logged_on,
						t.tickets_desired_completion,
						t.tickets_description,
						t.tickets_last_updated,
						t.tickets_hours_completed,
						t.tickets_process_id,
						u1.first_name,
						u1.last_name,
						u1.id AS logged_user_id,
					  	u2.username AS updated_user_username,
						u2.id AS updated_user_id,
						d.department_name,
						d.department_id,
						p.project_name,
						p.project_id,
					  	pr.process_name,
					  	pr.process_id
	   			   FROM tickets t,
			            users u1,
					    users u2,
						department d,
						project p,
					    process pr
	    		  WHERE t.tickets_company_id = {$company_id}
			  		AND t.tickets_id = {$ticket_id}
				    AND t.tickets_logged_user_id = u1.id
				    AND t.tickets_last_updated_by_user_id = u2.id
					AND t.tickets_department_id = d.department_id
					AND t.tickets_project_id = p.project_id
			  		AND t.tickets_process_id = pr.process_id;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
			return false;
		
		return $query->result_array();
	}
	
	/*
	 * Get the list of users assigned to a ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 */
	function get_assigned_users($company_id, $ticket_id)
	{
		$sql = "SELECT u.id AS assigned_user_id,
					   u.first_name AS assigned_user_first_name,
					   u.last_name AS assigned_user_last_name,
					   u.username
			      FROM tickets_user_list tul,
					   users u
			     WHERE tul.tickets_user_list_tickets_id = {$ticket_id}
			       AND tul.tickets_user_list_company_id = {$company_id}
			       AND tul.tickets_user_list_user_id = u.id;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
			return false;
		
		return $query->result_array();
	}
	
	/*
	 * Get the notes associated with a ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 */
	function get_ticket_notes($company_id, $ticket_id)
	{
		$sql = "SELECT u.first_name,
					   u.last_name,
					   u.id,
					   tn.tickets_notes_posted_date,
					   tn.tickets_notes_note
				  FROM tickets_notes tn,
					   users u
		 	    WHERE tn.tickets_notes_company_id = {$company_id}
		   		  AND tn.tickets_notes_ticket_id = {$ticket_id}
				  AND tn.tickets_notes_posted_user_id = u.id;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
			return false;
		
		return $query->result_array();
	}
	
	/*
	 * Get all of the tickets associated with the current ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 */
	function get_associated_tickets($company_id, $ticket_id)
	{
		$sql = "SELECT t.tickets_id,
					   t.tickets_title
		  		  FROM tickets_associated ta,
					   tickets t
		 		 WHERE ta.tickets_associated_tickets_linked_id = {$ticket_id}
			 	   AND ta.tickets_associated_tickets_originated_id = t.tickets_id
				UNION
				SELECT t.tickets_id,
					   t.tickets_title
		  		  FROM tickets_associated ta,
					   tickets t
		 		 WHERE ta.tickets_associated_tickets_originated_id = {$ticket_id}
			 	   AND ta.tickets_associated_tickets_linked_id = t.tickets_id;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
			return false;
		
		return $query->result_array();
	}
	
	/*
	 * Get the history of a ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 */
	function get_ticket_history($company_id, $ticket_id)
	{
		$sql = "SELECT th.tickets_history_timestamp,
					   (SELECT enum_long_description FROM enum WHERE enum_id = th.tickets_history_enum) AS history_description,
					   u.first_name,
					   u.last_name,
					   u.id
		  		  FROM tickets_history th,
					   users u
	  	 		 WHERE th.tickets_history_company_id = {$company_id}
	     		   AND th.tickets_history_ticket_id = {$ticket_id}
			 	   AND th.tickets_history_user_id = u.id
			  ORDER BY th.tickets_history_timestamp ASC;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
			return false;
		
		return $query->result_array();
	}
	
	/*
	 * Get all of the files uploaded to that ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 */
	function get_ticket_attachments($company_id, $ticket_id)
	{
		$sql = "SELECT t.tickets_file_description,
					   t.tickets_file_hash_name,
					   t.tickets_file_real_name,
					   t.tickets_file_upload_date,
					   t.tickets_file_id,
					   u.first_name,
					   u.last_name,
					   u.id
		  		  FROM tickets_files t,
					   users u
	   			 WHERE t.tickets_file_company_id = {$company_id}
		   		   AND t.tickets_file_tickets_id = {$ticket_id}
			 	   AND t.tickets_file_user_id = u.id;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
			return false;
		
		return $query->result_array();
	}
	
	/*
	 * Remove users assigned to ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 */
	function remove_users_assigned($company_id, $ticket_id)
	{
		$sql = "DELETE 
				  FROM tickets_user_list
			     WHERE tickets_user_list_tickets_id = {$ticket_id}
			       AND tickets_user_list_company_id = {$company_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Add users to the ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 * @user_id    The ID of the user that is being added to the ticket.
	 */
	function add_users_assigned($company_id, $ticket_id, $user_id)
	{
		$sql = "INSERT INTO tickets_user_list
						    (tickets_user_list_tickets_id,
					 		 tickets_user_list_user_id,
					 		 tickets_user_list_company_id)
	 				 VALUES ({$ticket_id},
				   			 {$user_id},
					 		 {$company_id});";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Updating a tickets information.
	 * 
	 * @company_id     The ID of the company that is being queried.
	 * @ticket_id      The ID of the ticket that is being queried.
	 * @title 	       The title of the ticket.
	 * @user_id	       The user that is updating the ticket.
	 * @completion     The date that the ticket needs completed.
	 * @severity       The severity of the ticket in an enum.
	 * @status	       The status of the ticket in an enum.
	 * @department_id  The department the ticket is assigned to.
	 * @project_id     The project the ticket is assigned to.
	 * @process_id     The process the ticket is assigned to.
	 * @hours_worked   The amount of hours currently worked. 
	 * @hours_allotted The amount of hours allotted to a ticket.
	 * @description    The ticket description.
	 */
	function update_ticket($company_id, $ticket_id, $title, $user_id, $completion, $severity, $status, $department_id, $project_id, $process_id, $hours_worked, $hours_allotted, $description)
	{
		$sql = "UPDATE tickets
	   			   SET tickets_department_id = {$department_id},
					   tickets_description = {$description},
					   tickets_desired_completion = {$completion},
					   tickets_hours_allotted = {$hours_allotted},
				   	   tickets_hours_completed = {$hours_worked},
					   tickets_last_updated = NOW(),
					   tickets_last_updated_by_user_id = {$user_id},
					   tickets_project_id = {$project_id},
					   tickets_severity = {$severity},
					   tickets_status = {$status},
					   tickets_title = {$title},
				 	   tickets_process_id = {$process_id}
		   	     WHERE tickets_id = {$ticket_id}
			 	   AND tickets_company_id = {$company_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Update the users table with their last ticket updated.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 * @user_ud	   The ID of the user that last updated the ticket.
	 */
	function update_last_ticket_updated($company_id, $ticket_id, $user_id)
	{
		$sql = "UPDATE users
				   SET user_last_ticket_updated = {$ticket_id}
				 WHERE id = {$user_id}
				   AND user_company_id = {$company_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Closing a ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 * @user_id    The ID of the user that closed the ticket.
	 */
	function close_ticket($company_id, $ticket_id, $user_id)
	{
		$sql = "UPDATE tickets
				   SET tickets_closed_date = NOW(),
				       tickets_closed_user_id = {$user_id}
				 WHERE tickets_id = {$ticket_id}
				   AND tickets_company_id = {$company_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Either re-opening a ticket or keeping it open already.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 */
	function open_ticket($company_id, $ticket_id)
	{
		$sql = "UPDATE tickets
				   SET tickets_closed_date = NULL,
				       tickets_closed_user_id = NULL
				 WHERE tickets_id = {$ticket_id}
				   AND tickets_company_id = {$company_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Adding a note to a ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 * @user_id    The ID of the user that is adding the note.
	 * @note	   The description of what the note is.
	 */
	function add_note($company_id, $ticket_id, $user_id, $note)
	{
		$sql = "INSERT INTO tickets_notes
							(tickets_notes_posted_user_id,
							 tickets_notes_note,
							 tickets_notes_posted_date,
							 tickets_notes_ticket_id,
							 tickets_notes_company_id)
		 			 VALUES ({$user_id},
							 {$note},
							 NOW(),
							 {$ticket_id},
							 {$company_id});";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Generate different types of ticket history.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 * @user_id	   The ID of the user that is doing the history.
	 * @enum	   The type of history this is.
	 */
	function generate_history($company_id, $ticket_id, $user_id, $enum)
	{
		$sql = "INSERT INTO tickets_history
						    (tickets_history_company_id,
							 tickets_history_ticket_id,
							 tickets_history_user_id,
							 tickets_history_enum,
							 tickets_history_timestamp)
					 VALUES ({$company_id},
							 {$ticket_id},
							 {$user_id},
							 {$enum},
							 NOW());";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Generate notification for the users home.
	 * 
	 * @company_id   The ID of the company that is being queried.
	 * @user_id	     Send out a notification to this user.
	 * @performed_id What user performed the action.
	 * @type		 The type of action performed.
	 * @type_id		 The relational ID that the type is associated with.
	 */
	function generate_notification($company_id, $user_id, $performed_id, $type, $type_id)
	{
		$sql = "INSERT INTO notifications
						    (notifications_user_id,
							 notifications_type,
							 notifications_type_id,
							 notifications_company_id,
							 notifications_timestamp,
							 notifications_user_performed_id)
					 VALUES ({$user_id},
							 {$type},
							 {$type_id},
							 {$company_id},
							 NOW(),
						     {$performed_id});";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Associate two tickets together.
	 * 
	 * @company_id 		  The ID of the company that is being queried.
	 * @user_id	   		  The ID of the user that is associating the ticket.
	 * @ticket_id  		  The original ticket ID.
	 * @associated_ticket The ticket ID that is being associated.
	 */
	function associate_tickets($company_id, $user_id, $ticket_id, $associated_ticket)
	{
		$sql = "INSERT INTO tickets_associated
							(tickets_associated_company_id,
							 tickets_associated_tickets_originated_id,
							 tickets_associated_tickets_linked_id,
							 tickets_associated_user_who_associated_id,
							 tickets_associated_timestamp)
					 VALUES ({$company_id},
							 {$ticket_id},
							 {$associated_ticket},
							 {$user_id},
							 NOW());";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Upload a file to the server.
	 * 
	 * @company_id     The ID of the company that is being queried.
	 * @ticket_id  	   The ID of the ticket that is being queried.
	 * @department_id  The department the ticket is assigned to.
	 * @project_id     The project the ticket is assigned to.
	 * @user_id		   The user doing the upload.
	 * @real_name	   The real name of the file when it comes in.
	 * @hash_name	   The secure hash the file gets when it is stored on the server.
	 * @type		   What is the file type.
	 * @size_kb		   Size of the file in kilobytes.
	 * @size_mb		   Size of the file in megabytes.
	 * @description	   The description of the file that was uploaded.
	 */
	function upload($company_id, $ticket_id, $department_id, $project_id, $user_id, $real_name, $hash_name, $type, $size_kb, $size_mb, $description)
	{
		$sql = "INSERT INTO tickets_files
						    (tickets_file_real_name,
							 tickets_file_hash_name,
							 tickets_file_type,
							 tickets_file_size_kb,
			         		 tickets_file_size_mb,
							 tickets_file_description,
							 tickets_file_company_id,
						     tickets_file_user_id,
							 tickets_file_tickets_id,
							 tickets_file_department_id,
							 tickets_file_project_id,
							 tickets_file_upload_date)
					 VALUES ({$real_name},
							 '{$hash_name}',
							 {$type},
							 {$size_kb},
							 {$size_mb},
							 {$description},
							 {$company_id},
							 {$user_id},
						     {$ticket_id},
							 {$department_id},
							 {$project_id},
							 NOW());";
							 
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Get ticket hash name so it can be removed from the server.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @file_id	   The ID of the file that we need to get the hash name for.
	 */
	function get_hash_name($company_id, $file_id)
	{
		$sql = "SELECT t.tickets_file_hash_name
				  FROM tickets_files t
			     WHERE t.tickets_file_company_id = {$company_id}
			       AND t.tickets_file_id = {$file_id};";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
			return false;
		
		return $query->result_array();
	}
	
	/*
	 * Remove a file from the server.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @file_id	   The ID of the file that is going to be removed.
	 */
	function remove_file($company_id, $file_id)
	{
		$sql = "DELETE 
				  FROM tickets_files
			     WHERE tickets_file_company_id = {$company_id}
			       AND tickets_file_id = {$file_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Insert new ticket.
	 * 
	 * @company_id     The ID of the company that is being queried.
	 * @user_id	       The user that is creating the ticket.
	 * @title 	       The title of the ticket.
	 * @severity       The severity of the ticket in an enum.
	 * @status	       The status of the ticket in an enum.
	 * @department_id  The department the ticket is assigned to.
	 * @project_id     The project the ticket is assigned to.
	 * @process_id     The process the ticket is assigned to.
	 * @hours_worked   The amount of hours currently worked. 
	 * @hours_allotted The amount of hours allotted to a ticket.
	 * @description    The ticket description.
	 * @completion     The date that the ticket needs completed.
	 */
	function insert_ticket($company_id, $user_id, $title, $severity, $status, $department_id, $project_id, $process_id, $hours_worked, $hours_allotted, $description, $completion)
	{
		$sql = "INSERT INTO tickets
							(tickets_company_id,
							 tickets_title,
							 tickets_severity,
							 tickets_status,
							 tickets_hours_allotted,
							 tickets_logged_on,
							 tickets_desired_completion,
							 tickets_description,
							 tickets_hours_completed,
							 tickets_process_id,
							 tickets_department_id,
							 tickets_project_id,
							 tickets_last_updated_by_user_id,
							 tickets_logged_user_id,
							 tickets_last_updated)
					  VALUES({$company_id},
					  		 {$title},
					  		 {$severity},
					  		 {$status},
					  		 {$hours_allotted},
					  		 NOW(),
					  		 {$completion},
					  		 {$description},
					  		 {$hours_worked},
					  		 {$process_id},
					  		 {$department_id},
					  		 {$project_id},
					  		 {$user_id},
					  		 {$user_id},
					  		 NOW());";
					  		 
		 $query = $this->db->simple_query($sql);
					  		 
  		 // If nothing was returned invalid query.
  		 if(!$query)
  		 	return false;
  		 
  		 return $this->db->insert_id();
	}
	
	/*
	 * Filter through tickets.
	 * 
	 * @company_id        The ID of the company that is being queried.
	 * @filter_severity   The severity that is going to be filtered.
	 * @filter_status	  The status that is going to be filtered.
	 * @filter_department The department that is going to be filtered.
	 * @filter_project    The project that is going to be filtered.
	 * @filter_process    The process that is going to be filtered.
	 * @filter_user		  The user that is going to be filtered.
	 */
	function get_company_filter_tickets($company_id, $filter_severity, $filter_status, $filter_department, $filter_project, $filter_process, $filter_user)
	{
		$filter_user1 = "";
		$filter_user2 = "";
		$filter_user3 = "";
		
		if($filter_severity)
			$filter_severity = "AND t.tickets_severity = {$filter_severity}";
		
		if($filter_status)
			$filter_status = "AND t.tickets_status = {$filter_status}";
		
		if($filter_department)
			$filter_department = "AND t.tickets_department_id = {$filter_department}";
		
		if($filter_project)
			$filter_project = "AND t.tickets_project_id = {$filter_project}";
		
		if($filter_process)
			$filter_process = "AND t.tickets_process_id = {$filter_process}";
		
		if($filter_user)
		{
			$filter_user1 = ", tickets_user_list tul";
			$filter_user2 = "AND t.tickets_id = tul.tickets_user_list_tickets_id";
			$filter_user3 = "AND tul.tickets_user_list_user_id = {$filter_user}";
		}
			
		$sql = "SELECT	t.tickets_id,
						t.tickets_title,
						(SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_status) tickets_status_description,
						(SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_severity) tickets_severity_description,
						t.tickets_desired_completion
				   FROM tickets t{$filter_user1}
				  WHERE t.tickets_company_id = {$company_id}
					{$filter_severity}
					{$filter_status}
					{$filter_department}
					{$filter_project}
					{$filter_process}
					{$filter_user2}
					{$filter_user3}
			   ORDER BY t.tickets_id DESC;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
			return false;
		
		return $query->result_array();
	}
	
	/*
	 * Deleting a ticket.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 */
	function delete_ticket($company_id, $ticket_id)
	{
		$sql = "DELETE 
			      FROM tickets
				 WHERE tickets_company_id = {$company_id}
				   AND tickets_id = {$ticket_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Getting all of the comments associated with a tickets file.
	 * 
	 * @company_id   The ID of the company that is being queried.
	 * @ticket_id    The ID of the ticket that is being queried.
	 * @$attachments The array of ID's of files.
	 */
	function get_file_comments($company_id, $ticket_id, $attachments)
	{
		$comments = array();
		
		if($attachments)
		{
			for($i = 0; $i < count($attachments); ++$i)
			{ 
				$sql = "SELECT u.first_name,
							   u.last_name,
							   u.id,
							   tfc.tickets_files_comments_creation_date,
							   tfc.tickets_files_comments_description
						  FROM tickets_files_comments tfc,
							   users u
						 WHERE tfc.tickets_files_comments_company_id = {$company_id}
						   AND tfc.tickets_files_comments_tickets_id = {$ticket_id}
						   AND tfc.tickets_files_comments_file_id = {$attachments[$i]['tickets_file_id']}
						   AND tfc.tickets_files_comments_user_id = u.user_id;";
				
				$query = $this->db->query($sql);
				$query = $query->result_array();
				
				array_push($comments, $query);
			}
		}
		
		return $comments;
	}
	
	/*
	 * Submitting a comment on a tickets file.
	 * 
	 * @company_id The ID of the company that is being queried.
	 * @ticket_id  The ID of the ticket that is being queried.
	 * @file_id	   The ID of the file that is getting the comment.
	 * @user_id    The ID of the user that is creating the comment.
	 * @comment	   The comment sent back in the POST.
	 */
	function comment_on_file($company_id, $ticket_id, $file_id, $user_id, $comment)
	{
		$sql = "INSERT INTO tickets_files_comments
						    (tickets_files_comments_company_id,
							 tickets_files_comments_tickets_id,
							 tickets_files_comments_file_id,
							 tickets_files_comments_user_id,
							 tickets_files_comments_description,
							 tickets_files_comments_creation_date,
							 tickets_files_comments_update_date)
					  VALUES({$company_id},
							 {$ticket_id},
							 {$file_id},
							 {$user_id},
							 {$comment},
							 NOW(),
							 NOW());";
		
		return $this->db->simple_query($sql);
	}
}